package goods_serv

import (
	"strconv"
	"strings"
	"ubt-aio-service/app/mod/goods_mod"
	"ubt-aio-service/app/pkg/db"
	"ubt-aio-service/app/utils"
)

type spu struct{}

var SpuServ = spu{}

// Add 新增
func (s *spu) Add(req *goods_mod.SpuSaveReq) *utils.ResJson {
	// 去掉主键，改为自动赋值模式
	sqlStr := "INSERT INTO goods_spu(fst_name, sec_name, spu_code, quanpin, jianpin, kind, brand_id, fst_type, sec_type, thr_type, costs, old_price, price, prvd_id, fst_img_id, fst_img_name, fst_img_url, file_ids, file_names, file_urls, visit_num, buy_num, cmt_num, grade, params, tag_ids, remarks, states, add_time, upd_time) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, 3, EXTRACT(epoch FROM NOW()), EXTRACT(epoch FROM NOW()))"
	return db.CudData(sqlStr, req.FstName, req.SecName, req.SpuCode, req.Quanpin, req.Jianpin, req.Kind, req.BrandId, req.FstType, req.SecType, req.ThrType, req.Costs, req.OldPrice, req.Price, req.PrvdId, req.FstImgId, req.FstImgName, req.FstImgUrl, req.FileIds, req.FileNames, req.FileUrls, req.VisitNum, req.BuyNum, req.CmtNum, req.Grade, req.Params, req.TagIds, req.Remarks)
}

// Exists 判断是否重复
func (s *spu) Exists(req *goods_mod.SpuExtVo) bool {
	// 注意修改对应的变量值
	sqlStr := "SELECT spu_id FROM goods_spu WHERE states=1 AND thr_type=$1 AND (fst_name=$2 OR spu_code=$3)"
	param := make([]any, 0)
	param = append(param, req.ThrType)
	param = append(param, req.FstName)
	param = append(param, req.SpuCode)
	if req.SpuId != "" {
		sqlStr += " AND spu_id!=$4"
		param = append(param, req.SpuId)
	}
	if res := db.GetOne(sqlStr, param...); res.Code == utils.Succeed.Code {
		return true
	}
	return false
}

// Update 修改
func (s *spu) Update(req *goods_mod.SpuSaveReq) *utils.ResJson {
	// 注意最后的索引值修改，以及去掉不需要修改的值
	sqlStr := "UPDATE goods_spu SET fst_name=$1, sec_name=$2, spu_code=$3, quanpin=$4, jianpin=$5, kind=$6, brand_id=$7, fst_type=$8, sec_type=$9, thr_type=$10, costs=$11, old_price=$12, price=$13, prvd_id=$14, fst_img_id=$15, fst_img_name=$16, fst_img_url=$17, file_ids=$18, file_names=$19, file_urls=$20, visit_num=$21, buy_num=$22, cmt_num=$23, grade=$24, params=$25, tag_ids=$26, remarks=$27, upd_time=EXTRACT(epoch FROM NOW()) WHERE spu_id=$28"
	// 需要将第一个值放到最后
	return db.CudData(sqlStr, req.FstName, req.SecName, req.SpuCode, req.Quanpin, req.Jianpin, req.Kind, req.BrandId, req.FstType, req.SecType, req.ThrType, req.Costs, req.OldPrice, req.Price, req.PrvdId, req.FstImgId, req.FstImgName, req.FstImgUrl, req.FileIds, req.FileNames, req.FileUrls, req.VisitNum, req.BuyNum, req.CmtNum, req.Grade, req.Params, req.TagIds, req.Remarks, req.SpuId)
}

// Process 处理
func (s *spu) Process(req *goods_mod.SpuProcReq) *utils.ResJson {
	var (
		param []any
		q     string
		n     = 1
	)
	param = append(param, req.States)
	idList := strings.Split(req.Ids, ",")
	for _, curId := range idList {
		n++
		q += "$" + strconv.Itoa(n) + ","
		param = append(param, curId)
	}
	q = q[:len(q)-1]
	sqlStr := "UPDATE goods_spu SET states=$1, upd_time=EXTRACT(epoch FROM NOW()) WHERE spu_id IN (" + q + ")"
	return db.CudData(sqlStr, param...)
}

// One 获取详情
func (s *spu) One(spuId string) *utils.ResJson {
	sqlStr := "SELECT a.spu_id, a.fst_name, a.sec_name, a.spu_code, a.quanpin, a.jianpin, a.kind, a.brand_id, b.brand_name, c.brand_code, " +
		"a.fst_type, c.type_name AS fst_type_name, c.type_code AS fst_type_code, a.sec_type, d.type_name AS sec_type_name, d.type_code AS sec_type_code, a.thr_type, e.type_name AS thr_type_name, e.type_code AS thr_type_code, " +
		"a.costs, a.old_price, a.price, a.prvd_id, f.prvd_name, f.prvd_code, a.fst_img_id, a.fst_img_name, a.fst_img_url, a.file_ids, a.file_names, a.file_urls, a.visit_num, a.buy_num, a.cmt_num, a.grade, a.params, a.tag_ids, a.remarks, a.states, a.add_time, a.upd_time " +
		"FROM goods_spu a " +
		"LEFT JOIN goods_brand b ON a.brand_id=b.brand_id " +
		"LEFT JOIN goods_type c ON a.fst_type=c.type_id " +
		"LEFT JOIN goods_type d ON a.sec_type=d.type_id " +
		"LEFT JOIN goods_type e ON a.thr_type=e.type_id " +
		"LEFT JOIN provider f ON a.prvd_id=f.prvd_id " +
		"WHERE a.spu_id=$1"
	return db.GetOne(sqlStr, spuId)
}

// Count 获取数量
func (s *spu) Count(req *goods_mod.SpuPageReq) *utils.ResJson {
	sqlStr := "SELECT COUNT(1) AS nums FROM goods_spu WHERE states=$1 AND kind=$2"
	param := make([]any, 0)
	var n = 2
	param = append(param, req.States)
	param = append(param, req.Kind)
	if req.Keywords != "" {
		sqlStr += " AND ("
		nameArr := strings.Split(req.Keywords, " ")
		for _, name := range nameArr {
			sqlStr += "fst_name LIKE '%'||$" + strconv.Itoa(n+1) + "||'%' OR " +
				"sec_name LIKE '%'||$" + strconv.Itoa(n+2) + "||'%' OR " +
				"spu_code LIKE '%'||$" + strconv.Itoa(n+3) + "||'%' OR "
			param = append(param, name)
			param = append(param, name)
			param = append(param, name)
			n += 3
		}
		sqlStr = sqlStr[:len(sqlStr)-4]
		sqlStr += ")"
	}
	if req.BrandId != "" {
		sqlStr += " AND brand_id=$" + strconv.Itoa(n+1)
		param = append(param, req.BrandId)
		n++
	}
	if req.TypeId != "" {
		sqlStr += " AND (fst_type=$" + strconv.Itoa(n+1) +
			" OR sec_type=$" + strconv.Itoa(n+2) +
			" OR thrType=$" + strconv.Itoa(n+3) + ") "
		param = append(param, req.TypeId)
		param = append(param, req.TypeId)
		param = append(param, req.TypeId)
		n += 3
	}
	if res := db.GetOne(sqlStr, param...); res.Code == utils.Succeed.Code && res.Data.(map[string]any)["nums"].(int64) > 0 {
		return res
	}
	return utils.NoData
}

// Page 获取分页列表
func (s *spu) Page(req *goods_mod.SpuPageReq) *utils.ResJson {
	sqlStr := "SELECT a.spu_id, a.fst_name, a.sec_name, a.spu_code, b.brand_name, b.brand_code, " +
		"c.type_name AS fst_type_name, c.type_code AS fst_type_code, d.type_name AS sec_type_name, d.type_code AS sec_type_code, e.type_name AS thr_type_name, e.type_code AS thr_type_code, " +
		"a.costs, a.old_price, a.price, f.prvd_name, f.prvd_code, a.fst_img_name, a.fst_img_url, a.visit_num, a.buy_num, a.cmt_num, a.grade, a.states, a.add_time " +
		"FROM goods_spu a " +
		"LEFT JOIN goods_brand b ON a.brand_id=b.brand_id " +
		"LEFT JOIN goods_type c ON a.fst_type=c.type_id " +
		"LEFT JOIN goods_type d ON a.sec_type=d.type_id " +
		"LEFT JOIN goods_type e ON a.thr_type=e.type_id " +
		"LEFT JOIN provider f ON a.prvd_id=f.prvd_id " +
		"WHERE a.states=$1 AND kind=$2"
	param := make([]any, 0)
	var n = 2
	param = append(param, req.States)
	param = append(param, req.Kind)
	if req.Keywords != "" {
		sqlStr += " AND ("
		nameArr := strings.Split(req.Keywords, " ")
		for _, name := range nameArr {
			sqlStr += "a.fst_name LIKE '%'||$" + strconv.Itoa(n+1) + "||'%' OR " +
				"a.sec_name LIKE '%'||$" + strconv.Itoa(n+2) + "||'%' OR " +
				"a.spu_code LIKE '%'||$" + strconv.Itoa(n+3) + "||'%' OR "
			param = append(param, name)
			param = append(param, name)
			param = append(param, name)
			n += 3
		}
		sqlStr = sqlStr[:len(sqlStr)-4]
		sqlStr += ")"
	}
	if req.BrandId != "" {
		sqlStr += " AND a.brand_id=$" + strconv.Itoa(n+1)
		param = append(param, req.BrandId)
		n++
	}
	if req.TypeId != "" {
		sqlStr += " AND (a.fst_type=$" + strconv.Itoa(n+1) +
			" OR a.sec_type=$" + strconv.Itoa(n+2) +
			" OR a.thrType=$" + strconv.Itoa(n+3) + ") "
		param = append(param, req.TypeId)
		param = append(param, req.TypeId)
		param = append(param, req.TypeId)
		n += 3
	}
	sqlStr += " ORDER BY a.upd_time DESC LIMIT $" + strconv.Itoa(n+1) + " OFFSET $" + strconv.Itoa(n+2)
	param = append(param, req.Limit)
	param = append(param, req.Offset)
	return db.GetAll(sqlStr, param...)
}
